# your_app/sales_invoice_print.py
import frappe
from frappe.utils import flt
from erpnext_oob.monkey_patches.data import money_in_words_zh


def before_print(doc, method=None, print_settings=None):
    """
    在打印前补充发票明细、聚合红冲信息、计算月度未结、以及除氢/电镀金额。
    这段逻辑来自你之前的 Server Script，已迁移到后端。
    """

    # 1) 追加红冲发票的明细（排除服务费料号 M2000）
    rows = frappe.db.sql(
        """
        SELECT 
            sii.item_code, sii.qty, sii.rate, 
            sii.amount, sii.`custom_备注`, 
            sii.base_net_amount, sii.net_amount
        FROM `tabSales Invoice` si
        INNER JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
        WHERE si.is_return = 1
          AND si.docstatus = 1
          AND si.custom_source_sales_invoice = %(docname)s
          AND sii.item_code != 'M2000'
        ORDER BY si.name ASC, sii.idx ASC
        """,
        {"docname": doc.name},
        as_dict=True,
    )

    for r in rows:
        doc.append(
            "items",
            {
                "item_code": r["item_code"],
                # "qty": r["qty"],
                # "rate": r["rate"],
                "amount": r["amount"],
                "custom_备注": r["custom_备注"],
                "base_net_amount": r["base_net_amount"],
                "net_amount": r["net_amount"],
            },
        )

    # 2) 收集 so_detail / dn_detail
    so_details = list({it.get("so_detail") for it in doc.items if it.get("so_detail")})
    dn_details = list({it.get("dn_detail") for it in doc.items if it.get("dn_detail")})

    sales_order_maps = {}
    delivery_note_maps = {}

    # 3) 拉取 Sales Order Item 映射
    if so_details:
        so = frappe.qb.DocType("Sales Order")
        soi = frappe.qb.DocType("Sales Order Item")

        soi_rows = (
            frappe.qb.from_(soi)
            .join(so)
            .on(so.name == soi.parent)
            .select(
                soi.name,
                so.name.as_("sales_order"),
                so.transaction_date,
                so.po_no,
                soi.custom_数量,
                soi.item_code,
                soi.qty,
                soi.rate,
                soi.amount,
                soi.delivered_qty,
                soi.billed_amt,
                soi.warehouse,
                soi.description,
            )
            .where(soi.name.isin(so_details))
        ).run(as_dict=True)

        sales_order_maps = {r["name"]: r for r in soi_rows}

    # 4) 拉取 Delivery Note Item 映射
    if dn_details:
        dn = frappe.qb.DocType("Delivery Note")
        dni = frappe.qb.DocType("Delivery Note Item")
        dni_rows = (
            frappe.qb.from_(dni)
            .join(dn)
            .on(dn.name == dni.parent)
            .select(
                dni.name,
                dn.name.as_("delivery_note"),
                dn.posting_date,
                dni.item_code,
                dni.qty,
                dni.rate,
                dni.amount,
                dni.warehouse,
                dni.so_detail,
                dni.description,
            )
            .where(dni.name.isin(dn_details))
        ).run(as_dict=True)

        delivery_note_maps = {r["name"]: r for r in dni_rows}

    # 5) 回填发票行上的 SO / DN 相关字段
    for it in (doc.items or []):
        so = sales_order_maps.get(it.get("so_detail")) or {}
        dn = delivery_note_maps.get(it.get("dn_detail")) or {}

        it.so_transaction_date = so.get("transaction_date") or ""
        it.so_qty = so.get("qty") or ""
        it.so_po_no = so.get("po_no") or ""
        it.so_custom_数量 = so.get("custom_数量") or ""

        it.dn_posting_date = dn.get("posting_date") or ""

    # 6) 计算与当前单据关联的红冲合计（剔除服务费 M2000）
    agg = frappe.db.sql(
        """
        SELECT
            COALESCE(SUM(CASE WHEN sii.item_code != 'M2000' THEN sii.amount          ELSE 0 END), 0) AS deduct_total,
            COALESCE(SUM(CASE WHEN sii.item_code != 'M2000' THEN sii.net_amount      ELSE 0 END), 0) AS deduct_net_total,
            COALESCE(SUM(CASE WHEN sii.item_code != 'M2000' THEN sii.base_amount     ELSE 0 END), 0) AS deduct_base_total,
            COALESCE(SUM(CASE WHEN sii.item_code != 'M2000' THEN sii.base_net_amount ELSE 0 END), 0) AS deduct_base_net_total
        FROM `tabSales Invoice` si
        JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
        WHERE si.is_return = 1
          AND si.docstatus = 1
          AND si.custom_source_sales_invoice = %(docname)s
        """,
        {"docname": doc.name},
        as_dict=True,
    )[0]

    deduct_total = agg.get("deduct_total") or 0
    deduct_net_total = agg.get("deduct_net_total") or 0
    deduct_base_total = agg.get("deduct_base_total") or 0
    deduct_base_net_tot = agg.get("deduct_base_net_total") or 0

    doc.collect_total = flt(doc.get("total")) + flt(deduct_total)
    doc.collect_net_total = flt(doc.get("net_total")) + flt(deduct_net_total)
    doc.collect_base_total = flt(doc.get("base_total")) + flt(deduct_base_total)
    doc.collect_base_net_total = flt(doc.get("base_net_total")) + flt(deduct_base_net_tot)

    # 7) 服务费红冲合计（仅 M2000）
    service = frappe.db.sql(
        """
        SELECT
            COALESCE(SUM(CASE WHEN sii.item_code = 'M2000' THEN sii.amount          ELSE 0 END), 0) AS service_total,
            COALESCE(SUM(CASE WHEN sii.item_code = 'M2000' THEN sii.net_amount      ELSE 0 END), 0) AS service_net_total,
            COALESCE(SUM(CASE WHEN sii.item_code = 'M2000' THEN sii.base_amount     ELSE 0 END), 0) AS service_base_total,
            COALESCE(SUM(CASE WHEN sii.item_code = 'M2000' THEN sii.base_net_amount ELSE 0 END), 0) AS service_base_net_total
        FROM `tabSales Invoice` si
        JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
        WHERE si.is_return = 1
          AND si.docstatus = 1
          AND si.custom_source_sales_invoice = %(docname)s
        """,
        {"docname": doc.name},
        as_dict=True,
    )[0]

    doc.service_charge = service.get("service_total") or 0

    # 8) 按月份计算未结清账款（不含当月）
    rows = frappe.db.sql(
        """
        SELECT
          DATE_FORMAT(si.posting_date, '%%c月') AS month,
          ROUND(SUM(si.outstanding_amount), 2) AS outstanding_amount
        FROM `tabSales Invoice` si
        WHERE si.docstatus = 1
          AND si.customer = %(doccustomer)s
          AND ABS(si.outstanding_amount) > 0
          AND si.posting_date < DATE_FORMAT(CURDATE(), '%%Y-%%m-01')  -- 排除当月
        GROUP BY YEAR(si.posting_date), MONTH(si.posting_date)
        ORDER BY YEAR(si.posting_date), MONTH(si.posting_date)
        """,
        {"doccustomer": doc.customer},
        as_dict=True,
    )

    doc.monthly_outstanding = [
        {"month": r["month"], "outstanding_amount": float(r["outstanding_amount"])} for r in rows
    ]
    doc.monthly_outstanding_total = (
        round(sum(x["outstanding_amount"] for x in doc.monthly_outstanding), 2)
        if doc.monthly_outstanding
        else 0.0
    )

    # 9) 计算除氢价格和电镀价格
    for it in (doc.items or []):
        qty = flt(it.get("qty"))
        dehyd = flt(it.get("custom_dehydrogenation_unit_price"))
        plat = flt(it.get("custom_plating_unit_price"))

        it.custom_dehydro_amount = flt(qty * dehyd, 3)
        it.custom_plating_amount = flt(qty * plat, 3)

    doc.so_total_qty = sum(flt(i.get("so_qty")) for i in (doc.items or []))
    doc.custom_dehydro_total = sum(flt(i.get("custom_dehydro_amount")) for i in (doc.items or []))
    doc.custom_plating_total = sum(flt(i.get("custom_plating_amount")) for i in (doc.items or []))

    doc.collect_total_cn = money_in_words_zh(doc.collect_total)
